Relational Databases and SQL Fundamentals using Db2 V11.5 and Data Management Console

Course Outline

Understanding a Table

• Create Company Directory Table
• Select all rows from the Directory Table

Data Modelling and Database Design

• Create Data Model and SQL to Create Tables
• Create a SQL Statement to Obtain Definitions from the Catalog
• Create Basic Select statements for Some Reports
• Retrieve basic employee information including employee last name and firstname only.
• Retrieve Basic Department Information
• Select employee names with the name of the department they work for.
• Create Primary Keys
• Create new tables by issuing the following SQL statements:
• Create a primary key for emp_temp on the empno column, and for the dept_temp table, on column deptno.
• Try to add the employee rows to the emp_temp table again. What happens?
• Create a Foreign Key
• Define a parent-child relationship between the dept_temp table and emp_temp table
• Test the Foreign Constraint
• Create Triggers
• Create New COMPANY_STATS table
• Create Insert Trigger to Keep COMPANY_STATS Up To Date
• Create Delete Trigger to Keep COMPANY_STATS Up To Date

How an End user uses Db2

• Become familiar with the Data
• Increase all the emp_temp salaries by $1000. Check the total salaries before and after.
• Decrease all the emp_temp salaries by $2000 for department A00. Check the total salary for this department before and after.
• Create a report with basic employee information from the employee table, sorting by last name and first name.
• Modify the last report, adding the salary and bonus and name the new column "INCOME". Show only employees where salary + bonus > $50,000.
• Create a report showing the employee(s) with the maximum salary.
• Create a report showing the average salary for all employees.
• Create a report showing the count of employees for each department.
• For the last report, only show the departments with more than 4 employees, and give the second heading a more meaningful name, such as '#EMPLOYEES'.
• Create a report showing the employee’s last name, concatenated with a comma and the first letter of their first name
• Create and populate a table called CATALOG
• Create a Report on the CATALOG1 table
• Join Department and Employee tables
• Optional: Use Db2 Interactive Mode and Execute a Script

How a Programmer uses Db2

• Making changes with autocommit turned off
• Use the Db2 Explain facility in Data Management Console to Explain your previous SQL Join
• Select a column that does not exist from the employee table. What message is returned?
• Create a stored procedure to select from the employee table

Tablespaces, Catalog Views and Security

• Create a new tablespace, userspace2
• Create a copy of the employee table in userspace2
• Add Rows from One Table to another using Select
• Use the Db2 catalog to find information about the employee table
• Use the Db2 Catalog to find Information about Columns in the Employee Table
• Connect to Db2 as another user and try to select from the employee table.

Locking, Check Constraints, Special Registers, Indexes, Views, Reorg, Runstats

• Explore Locking
• Explore Db2 Special Registers
• Create an Index
• Reorg and Runstats
• Create a View
• Select all rows from the view, sorting by employee lastname, then employee first name

Additional Course Delivery Option

• For those requiring only a very basic introduction to SQL, there is an option to cover only the first three units, together with associated labs, leaving out following topics such as How a Programmer Uses Db2. This option is conducted over two days, instead of three.